#!/bin/bash 

source /etc/profile

if [ $# -eq 1 ]; then
	targetHour=$1
else
	targetHour=`date -d "-1 hours" +"%Y%m%d%H"`
fi
path=$(cd $(dirname $0);pwd)
parentPath=$(dirname ${path})
targetDate=${targetHour:0:8}
if [ ! -d "${path}/${targetDate}" ];then
	mkdir -p ${path}/${targetDate}
fi
day=$(date -d"${targetDate}" "+%Y-%m-%d")

#恶意点击ip (pv>=100) or (跳出率100%，访问次数超过3（老访客），都是通过付费广告进入)
hive -e "set mapred.job.name=[NAS][tools][filter_ip][${targetDate}];use formatlog;insert overwrite local directory '${path}/${targetDate}/${targetHour}_ip' SELECT cid,sid,${targetDate},info.col1 AS ip,SUM(pv) AS pv,COUNT(DISTINCT nasid) AS uv,SUM(IF(pv=1,1,0)) AS bounce,SUM(visit_time)/SUM(pv) AS avg_visittime,COUNT(DISTINCT session) AS session,MAX(info.col3) AS refererType FROM (SELECT cid,sid,session,COUNT(1) as pv,MAX(nasid) AS nasid,((MAX(time)-MIN(time))/1000+60) AS visit_time,MIN(struct(ip,ispay,refererType)) AS info FROM nas_tracking_format_hour WHERE day=${targetDate} GROUP BY cid,sid,session) T GROUP BY cid,sid,info.col1 HAVING (SUM(pv)>=100) OR (session>3 AND session=bounce AND session=SUM(IF(info.col2='False',0,1)))"
wait
cat ${path}/${targetDate}/${targetHour}_ip/* > ${path}/${targetDate}/${targetHour}_ips
wait
source ${parentPath}/configs/mysql_config
python ${path}/load_ip_to_mysql.py ${path}/${targetDate}/${targetHour}_ips ${host} ${port} ${user} ${password} ${db} ${day}